﻿using KPIS.GERP.Models;
using KPIS.GERP.Models.ICM;
using KPIS.GERP.Models.ICM.ViewModel;
using KPIS.GERP.Models.SYS;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Entity;
using System.Linq;
using System.Linq.Dynamic;
using System.ServiceModel;
using System.Web.Mvc;

namespace KPIS.GERP.WCF.GIMS
{
    [ServiceContract]
    public interface IPrePurchaseRequestItemService
    {
        [OperationContract]
        IEnumerable<PrePurchaseRequestItem> Select(PrePurchaseRequestItem criteriaModel = null, List<string> includeSubObjectString = null);

        [OperationContract]
        IEnumerable<PrePurchaseRequestItem> SelectNotGathering(int PprId, int BgYear, int BgSourceId, int PcTypeId);

        [OperationContract]
        int Insert(PrePurchaseRequestItem model, int createByUserId);

        [OperationContract]
        void Update(PrePurchaseRequestItem model, int updateByUserId);

        [OperationContract]
        void Delete(int id, int deleteByUserId);

        [OperationContract]
        bool CheckingGatheringPR(IEnumerable<int> pprItemIds, out string parameter);

        [OperationContract]
        IEnumerable<PrePurchaseRequestItem> SelectGathering(int[] selectIds);

        [OperationContract]
        bool CheckAmount(int PprId);
    }

    public class PrePurchaseRequestItemService : IPrePurchaseRequestItemService
    {
        public IEnumerable<PrePurchaseRequestItem> Select(PrePurchaseRequestItem criteriaModel = null, List<string> includeSubObjectString = null)
        {
            try
            {
                using (var context = new GERPContext(false))
                {
                    IQueryable<PrePurchaseRequestItem> query = context.PrePurchaseRequestItems;

                    if (includeSubObjectString != null)
                    {
                        query = includeSubObjectString.Aggregate(query, (current, includePath) => current.Include(includePath));
                    }

                    if (criteriaModel != null)
                    {
                        object[] param;
                        query = query.Where(KPIS.GERP.Models.Class.Utility.GenerateWhereString(criteriaModel, out param), param);
                    }

                    var x = query.ToList();
                    return x;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public IEnumerable<PrePurchaseRequestItem> SelectNotGathering(int PprId, int BgYear, int BgSourceId, int PcTypeId)
        {
            try
            {
                using (var context = new GERPContext(false))
                {
                    IQueryable<PrePurchaseRequestItem> query = context.PrePurchaseRequestItems;

                    //query = query.Include("PrePurchaseRequest")
                    //    .Include("MasBudgetExpense")
                    //    .Include("MasResourceItem")
                    //    .Where(m => m.PrItemId == null
                    //        && (PprId == null || PprId == 0 ? true : m.PprId == PprId)
                    //        && (BgSourceId == null || BgSourceId == 0 ? true : m.BgSourceId == BgSourceId)
                    //        && (BgYear == null || BgYear == 0 ? true : m.PrePurchaseRequest.BgYear == BgYear)
                    //        && (PcTypeId == null || PcTypeId == 0 ? true : m.PrePurchaseRequest.PcTypeId == PcTypeId));

                    query = query.Include("PrePurchaseRequest")
                                .Include("MasBudgetExpense")
                                .Include("MasResourceItem")
                                .Where(pprItem => pprItem.PrItemId == null);

                    // remark: these Id is int not null, no need to check null
                    if(PprId != 0)
                    {
                        query = query.Where(pprItem => pprItem.PprId == PprId);
                    }

                    if(BgSourceId != 0)
                    {
                        query = query.Where(pprItem => pprItem.BgSourceId == BgSourceId);
                    }

                    if(BgYear != 0)
                    {
                        query = query.Where(pprItem => pprItem.PrePurchaseRequest.BgYear == BgYear);
                    }

                    if(PcTypeId != 0)
                    {
                        query = query.Where(m => m.PrePurchaseRequest.PcTypeId == PcTypeId);
                    }

                    var x = query.ToList();
                    return x;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public int Insert(PrePurchaseRequestItem model, int createByUserId)
        {
            try
            {
                using (var context = new GERPContext())
                {
                    model.CreatedBy = createByUserId;
                    model.CreatedWhen = DateTime.Now;
                    context.PrePurchaseRequestItems.Add(model);
                    context.SaveChanges();

                    return model.PprItemId;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void Update(PrePurchaseRequestItem model, int updateByUserId)
        {
            try
            {
                using (var context = new GERPContext())
                {
                    context.Entry(model).State = EntityState.Modified;
                    model.UpdatedBy = updateByUserId;
                    model.UpdatedWhen = DateTime.Now;
                    context.SaveChanges();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void Delete(int id, int deleteByUserId)
        {
            try
            {
                using (var context = new GERPContext())
                {
                    var modelToDelete = context.PrePurchaseRequestItems.Find(id);
                    if (modelToDelete == null) return;

                    context.PrePurchaseRequestItems.Remove(modelToDelete);
                    context.SaveChanges();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public bool CheckingGatheringPR(IEnumerable<int> pprItemIds, out string parameter)
        {
            try
            {
                List<PrePurchaseRequestItem> lstModel = new List<PrePurchaseRequestItem>();
                using (var context = new GERPContext())
                {
                    lstModel.AddRange(context.PrePurchaseRequestItems.Where(o => pprItemIds.Contains(o.PprItemId)).ToList());
                } 
                bool ret = true;
                string message = "สามารถรวมรายการได้";
                if (lstModel.Count <= 0)
                {
                    message = "ไม่มีข้อมูล";
                    ret = false;
                }

                int? val = lstModel.Select(s => s.BgSourceId).FirstOrDefault().Value;
                if (!lstModel.All(x => x.BgSourceId == val))
                {
                    message = "ไม่สามารถรวมรายการได้เนื่องจากแหล่งเงินไม่ตรงกัน";
                    ret = false;
                }

                val = lstModel.Select(s => s.RegularId).FirstOrDefault().Value;
                if (ret && !lstModel.All(x => x.RegularId.Value == val)) 
                {
                    message = "ไม่สามารถรวมรายการได้เนื่องจากระเบียบไม่ตรงกัน";
                    ret = false;
                }
               

                if(ret){
                    using (var context = new GERPContext(false))
                    {
                        List<vmResItem> vmResItemlst = new List<vmResItem>();
                        foreach (PrePurchaseRequestItem item in lstModel)
                        {
                            if (item.ResItemId != null)
                            {
                                vmResItem vmitem = new vmResItem();
                                vmitem.ResItemId = (int)item.ResItemId;
                                vmResItemlst.Add(vmitem);
                            }
                        }


                        IQueryable<MasResourceItem> query = context.MasResourceItems;
                        string[] includeSubObjectString = { "MasResourceSubGroup" };
                        if (includeSubObjectString != null)
                        {
                            query = includeSubObjectString.Aggregate(query, (current, includePath) => current.Include(includePath));
                        }

                        if (vmResItemlst.Count > 0)
                        {
                            foreach (vmResItem vmitem in vmResItemlst)
                            {
                                object[] param;
                                query = query.Where(KPIS.GERP.Models.Class.Utility.GenerateWhereString(lstModel.Where(w => w.ResItemId == vmitem.ResItemId), out param), param);
                                vmitem.ResItemName = query.FirstOrDefault().Name;
                                vmitem.ResSubGroupId = query.FirstOrDefault().ResSubGrpId ?? 0;
                                vmitem.ResSubGroupName = query.FirstOrDefault().MasResourceSubGroup.Name;
                                vmitem.ResGroupId = query.FirstOrDefault().MasResourceSubGroup.ResGrpId ?? 0;
                            }

                        }

                        val = vmResItemlst.Select(s => s.ResGroupId).FirstOrDefault();
                        if (!vmResItemlst.All(x => x.ResGroupId == val))
                        {
                            message = "ไม่สามารถรวมรายการได้เนื่องจากกลุ่มรายการพัสดุไม่ตรงกัน";
                            ret = false;
                        }
                       // var x = query.Join(context.MasResourceItems, u => u.ResItemId, ur => ur.ResItemId, (u, ur) => u);
                    
                    }
                }
                parameter = message;
                return ret;

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public IEnumerable<PrePurchaseRequestItem> SelectGathering(int[] selectIds)
        {
            try
            {
                if (selectIds.Length == 0)
                    return new List<PrePurchaseRequestItem>();

                using (var context = new GERPContext(false))
                {
                    var query = from ppri in context.PrePurchaseRequestItems
                                join ppr in context.PrePurchaseRequests on ppri.PprId equals ppr.PprId
                                join exp in context.MasBudgetExpenses on ppri.BgExpId equals exp.BgExpId
                                join res in context.MasResourceItems on ppri.ResItemId equals res.ResItemId
                                where selectIds.Contains(ppri.PprItemId)
                                group ppri by new { ppri.ResItemId, ppri.BgExpId, ppri.RegularId } into g
                                //orderby new { g.ResItemId, g.BgExpId }
                                //select ppri;
                                select new PrePurchaseRequestItem()
                                {
                                    Qty = g.Sum(ppri => ppri.Qty),
                                    Amount = g.Select(d => d.Amount).FirstOrDefault(),
                                    BgExpId = g.Select(d => d.BgExpId).FirstOrDefault(),
                                    BgSourceId = g.Select(d => d.BgSourceId).FirstOrDefault(),
                                    Descr = g.Select(d => d.Descr).FirstOrDefault(),
                                    MasBudgetExpense = g.Select(d => d.MasBudgetExpense).FirstOrDefault(),
                                    MasBudgetSource = g.Select(d => d.MasBudgetSource).FirstOrDefault(),
                                    MasPurchaseRegular = g.Select(d => d.MasPurchaseRegular).FirstOrDefault(),
                                    MasResourceItem = g.Select(d => d.MasResourceItem).FirstOrDefault(),
                                    PprId = g.Select(d => d.PprId).FirstOrDefault(),
                                    PprItemId = g.Select(d => d.PprItemId).FirstOrDefault(),
                                    PrePurchaseRequest = g.Select(d => d.PrePurchaseRequest).FirstOrDefault(),
                                    Price = g.Select(d => d.Price).FirstOrDefault(),
                                    PrItemId = g.Select(d => d.PrItemId).FirstOrDefault(),
                                    ProjectOperationExpense = g.Select(d => d.ProjectOperationExpense).FirstOrDefault(),
                                    ProjOperExpId = g.Select(d => d.ProjOperExpId).FirstOrDefault(),
                                    PurchaseRequestItem = g.Select(d => d.PurchaseRequestItem).FirstOrDefault(),
                                    RegularId = g.Select(d => d.RegularId).FirstOrDefault(),
                                    ResItemId = g.Select(d => d.ResItemId).FirstOrDefault(),
                                    StdPrice = g.Select(d => d.StdPrice).FirstOrDefault(),
                                };

                    var x = query.ToList();
                    return x;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public bool CheckAmount(int PprId)
        {
            DBM.DBManager dbManager = new DBM.DBManager(DBM.DataProvider.SqlServer, System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"].ToString());

            try
            {
                dbManager.Open();

                using (var db = new GERPContext(false))
                {
                    var sumAmounts = (from ppri in db.PrePurchaseRequestItems
                                      join ope in db.ProjectOperationExpenses on ppri.ProjOperExpId equals ope.ProjOperExpId
                                      join pe in db.ProjectExpenses on ope.ProjExpId equals pe.ProjExpId
                                      join e in db.MasBudgetExpenses on pe.BgExpId equals e.BgExpId
                                      where ppri.PprId == PprId
                                      group ppri by new { ppri.ProjectOperationExpense.ProjOperId, pe.BgSourceId, e.BgExpGrpId } into g
                                      select new { ProjOperId = g.Key.ProjOperId, BgSourceId = g.Key.BgSourceId, BgExpGrpId = g.Key.BgExpGrpId, Amount = g.Sum(o => o.Amount) })
                                     .ToList();

                    foreach (var sumAmount in sumAmounts)
                    {
                        dbManager.CreateParameters(3);

                        dbManager.AddParameters(0, "@bgm_proj_oper_seq", sumAmount.ProjOperId);
                        dbManager.AddParameters(1, "@bgm_source_seq", sumAmount.BgSourceId);
                        dbManager.AddParameters(2, "@bgm_exp_grp_seq", sumAmount.BgExpGrpId);
                        #region query
                        string strSQL = @"
select coalesce(oe.amount, 0)
	- (coalesce(po.amount, 0) - coalesce(mrpo.amount, 0))
	- coalesce(mr.amount, 0) + coalesce(r.amount, 0) as remain_amount
from bgm_project_operation o
left join (select oe.bgm_proj_oper_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq
	, sum(coalesce(rpt.cal_amount ,0)) as amount
	from bgm_project_operation_expense oe
	left join bgm_project_operation_report rpt on oe.bgm_proj_oper_exp_seq = rpt.bgm_proj_oper_exp_seq
	left join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
	left join bgm_mas_expense e on pe.bgm_exp_seq = e.bgm_exp_seq
	where oe.record_status not like 'D'
	and rpt.record_status not like 'D'
	and rpt.rpt_type = 1
	group by oe.bgm_proj_oper_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq
	) oe on o.bgm_proj_oper_seq = oe.bgm_proj_oper_seq

left join (select ope.bgm_proj_oper_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq
	, sum((COALESCE(quo.NetAmount, 0) / COALESCE(quo.Qty, 1)) * ppri.Qty) as amount
	from IcmPurchaseRequestItem pri
  inner join IcmPurchaseRequest pr on pri.PrId = pr.PrId
	inner join IcmBidderQuotation quo on pri.PrItemId = quo.PrItemId
	inner join IcmPurchaseOrder po on quo.BidderId = po.BidderId and po.RecordStatus = 'A'
	inner join (select ppri.PrItemId, ppri.BgSourceId, ppri.ProjOperExpId, sum(ppri.Qty) as Qty
		from IcmPrePurchaseRequestItem ppri
		group by ppri.PrItemId, ppri.BgSourceId, ppri.ProjOperExpId
	) ppri on pri.PrItemId = ppri.PrItemId
	inner join bgm_project_operation_expense ope on ppri.ProjOperExpId = ope.bgm_proj_oper_exp_seq
	left join bgm_project_expense pe on ope.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
	left join bgm_mas_expense e on pe.bgm_exp_seq = e.bgm_exp_seq
	where po.RecordStatus = 'A'
	and po.Closed = 0
	and pri.RecordStatus <> 'D'
	group by ope.bgm_proj_oper_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq
) po on o.bgm_proj_oper_seq = po.bgm_proj_oper_seq
	and oe.bgm_source_seq = po.bgm_source_seq
	and oe.bgm_exp_grp_seq = po.bgm_exp_grp_seq

left join (select mr.bgm_proj_oper_seq, mr.bgm_source_seq, mr.bgm_exp_grp_seq
	, sum(coalesce(mrd.amount, 0)) as amount
	from fim_money_request_dtls mrd
	inner join fim_money_requests mr on mrd.money_req_seq = mr.money_req_seq
	inner join IcmDeliveryNote dn on mr.icm_dn_seq = dn.DnId
	inner join IcmPurchaseOrder po on dn.PoId = po.PoId
	where mr.record_status = 'A'
	and mr.event_seq = 11
	and po.Closed = 0
	and mrd.record_status <> 'D'
	group by mr.bgm_proj_oper_seq, mr.bgm_source_seq, mr.bgm_exp_grp_seq
) mrpo on o.bgm_proj_oper_seq = mrpo.bgm_proj_oper_seq
	and oe.bgm_source_seq = mrpo.bgm_source_seq
	and oe.bgm_exp_grp_seq = mrpo.bgm_exp_grp_seq

left join (select mr.bgm_proj_oper_seq, mr.bgm_source_seq, mr.bgm_exp_grp_seq
	, sum(coalesce(mrd.amount, 0)) as amount
	from fim_money_request_dtls mrd
	inner join fim_money_requests mr on mrd.money_req_seq = mr.money_req_seq
	where mr.record_status = 'A'
	and mrd.record_status <> 'D'
	group by mr.bgm_proj_oper_seq, mr.bgm_source_seq, mr.bgm_exp_grp_seq
) mr on o.bgm_proj_oper_seq = mr.bgm_proj_oper_seq
	and oe.bgm_source_seq = mr.bgm_source_seq
	and oe.bgm_exp_grp_seq = mr.bgm_exp_grp_seq

left join (select mr.bgm_proj_oper_seq, p.bgm_source_seq, mr.bgm_exp_grp_seq
	, sum(coalesce(mrd.amount, 0)) as amount
	from fim_payments p
	inner join fim_money_request_dtls mrd on p.money_req_seq = mrd.money_req_seq
	inner join fim_money_requests mr on p.money_req_seq = mr.money_req_seq
	where p.record_status = 'A'
	and mrd.record_status <> 'D'
	group by mr.bgm_proj_oper_seq, p.bgm_source_seq, mr.bgm_exp_grp_seq
) p on o.bgm_proj_oper_seq = p.bgm_proj_oper_seq
	and oe.bgm_source_seq = p.bgm_source_seq
	and oe.bgm_exp_grp_seq = p.bgm_exp_grp_seq

left join (select mr.bgm_proj_oper_seq, r.bgm_source_seq, mr.bgm_exp_grp_seq
	, sum(coalesce(r.amount, 0)) as amount
	from fim_receives r
	inner join fim_money_requests mr on r.money_req_seq = mr.money_req_seq
	where r.record_status not in ('D', 'C')
	group by mr.bgm_proj_oper_seq, r.bgm_source_seq, mr.bgm_exp_grp_seq
) r on o.bgm_proj_oper_seq = r.bgm_proj_oper_seq
	and oe.bgm_source_seq = r.bgm_source_seq
	and oe.bgm_exp_grp_seq = r.bgm_exp_grp_seq

where oe.bgm_proj_oper_seq = @bgm_proj_oper_seq
AND oe.bgm_source_seq = @bgm_source_seq
AND oe.bgm_exp_grp_seq = @bgm_exp_grp_seq
";
                        #endregion
                        DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                        decimal sumReport = 0;

                        if (dtb.Rows.Count > 0)
                        {
                            sumReport = Convert.ToDecimal(dtb.Rows[0][0]);
                        }

                        //var sumReport = (from ope in db.ProjectOperationExpenses
                        //                 join rpt in db.ProjectOperationReports on ope.ProjOperExpId equals rpt.ProjOperExpId
                        //                 where ope.ProjOperId == sumAmount.ProjOperId
                        //                     && rpt.RecordStatus != RecordStatus.DeletedStatus
                        //                 select rpt)
                        //                 .Sum(o => o.CalAmount);

                        if (sumReport < sumAmount.Amount)
                        {
                            return false;
                        }
                    }
                }

                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
    }
}
